from sqlalchemy import Column, MetaData, ForeignKey,create_engine, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, backref, relation
from sqlalchemy.types import Binary, Date, Integer, String, Boolean
from sqlalchemy.schema import Sequence
import xml.parsers.expat
from sqlalchemy.sql.expression import text
import urllib2
import re
Base = declarative_base()
offline_mode = True
inventory_offline = False
invoice_offline = False

role_actions = Table('role_actions_rel', Base.metadata,
    Column('user_role_id', Integer, ForeignKey('user_roles.id')),
    Column('action_id', Integer, ForeignKey('actions.id'))
)
xml_sincwithinvoicemodule_NO = """<?xml version="1.0"?>
        <catalogo>
        <plan idplan='1'>
            <nombre>Prendido flex 666</nombre>
            <descripcion>ndesc</descripcion>
            <servicio idservicio='1' obligatorio='si'>
                <nombre>internet</nombre>
                <tamano>100</tamano>
                <dimension>mensajes</dimension>
                <red> si o no</red>
                <valor>8000</valor>
                <descripcion>servicio de mensajit</descripcion>
            </servicio>
            
            <servicio idservicio='2' obligatorio='no'>
                <nombre>mensajitos ilimitados</nombre>
                <tamano>100</tamano>
                <dimension>mensajes</dimension>
                <red> si o no</red>
                <valor>8000</valor>
                <descripcion>servicio de mensajit</descripcion>
            </servicio>
            <servicio idservicio='3' obligatorio='no'>
                <nombre>correo de vox</nombre>
                <tamano>100</tamano>
                <dimension>mensajes</dimension>
                <red> si o no</red>
                <valor>8000</valor>
                <descripcion>servicio de mensajit</descripcion>
            </servicio>
        </plan>
        
        <plan idplan='2'>
            <nombre>plansito feliz</nombre>
            <descripcion>ndesc</descripcion>
            <servicio idservicio='4' obligatorio='si'>
                <nombre>llamadas a gringolandia gratis</nombre>
                <tamano>100</tamano>
                <dimension>mensajes</dimension>
                <red> si o no</red>
                <valor>8000</valor>
                <descripcion>servicio de mensajit</descripcion>
            </servicio>
            <servicio idservicio='5' obligatorio='no'>
                <nombre>mensajito2</nombre>
                <tamano>100</tamano>
                <dimension>mensajes</dimension>
                <red> si o no</red>
                <valor>8000</valor>
                <descripcion>servicio de mensajit</descripcion>
            </servicio>
            </plan>
        </catalogo>	
        """
        
xml_sincwithinvoicemodule = """
        <catalogo>
	<plan idplan='1'         >
		<nombre>amigo                    </nombre>
		<descripcion>es un plan                                   </descripcion>
			<servicio idservicio='1'          obligatorio='SI'   >
				<nombre>serv1                    </nombre>
				<tamano>100</tamano>

				<dimension>llamadas       </dimension>
				<red>SI</red>
				<valor>10</valor>
				<descripcion>ES UN SERVICIO                               </descripcion>
			</servicio>
			<servicio idservicio='2'          obligatorio='SI'   >
				<nombre>serv2                    </nombre>

				<tamano>1</tamano>
				<dimension>TELEFONO       </dimension>
				<red>SI</red>
				<valor>10</valor>
				<descripcion>COBRO APERTURA DE LINEA                      </descripcion>
			</servicio>

	</plan>
</catalogo>
"""        
    
        
xml_getIncidenceHistory = """<report>
    <incidencias>
        <id>1</id>
        <cliente>Gallo</cliente>
        <cola>3</cola>
        <incidencia>Se cago algo</incidencia>
        <solucion>Se arreglo asi</solucion>
        <inicio>TIMESTAMP</inicio>
        <fin>TIMESTAMP</fin>
        <fecha>DATE</fecha>
    </incidencias>
    <incidencias>
        <id>2</id>
        <cliente>Gallo</cliente>
        <cola>3</cola>
        <incidencia>Se cago algo</incidencia>
        <solucion>Se arreglo asi</solucion>
        <inicio>TIMESTAMP</inicio>
        <fin>TIMESTAMP</fin>
        <fecha>DATE</fecha>
    </incidencias>
</report>"""



xml_getInvoiceHistory = """
<facturas>
    <factura cid="66">
        <cuenta>Gallo</cuenta>
        <fechaExpiracion>01012000</fechaExpiracion>
        <pago>si</pago>
        <monto>10</monto>
        <saldo>10</saldo>
    </factura>

    <factura cid="67">
        <cuenta>Gallo</cuenta>
        <fechaExpiracion>01012000</fechaExpiracion>
        <pago>no</pago>
        <monto>10</monto>
        <saldo>10</saldo>
    </factura>

</facturas>
"""

xml_transaction = """<?xml version="1.0"?>
<transaccion id='##' tipo='takeNumeros'>
    <status>ok</status>  
    <mensaje>numero no disponible</mensaje>
    <terminal id_producto='4' id_terminal='834' />
</transaccion>
"""

xml_getBrandCatalog = """<marcas producto='terminales'>
            <marca id="2">nokia</marca>
            <marca id="5">motorola</marca>
            <marca id="6">diablo</marca>
        </marcas>"""

xml_getTags = """
<tags producto='terminales'>
    <tag>moviles</tag>
    <tag>fijos</tag>
    <tag>internet</tag>
    <tag>blutut</tag>
    <tag>wifi</tag>
</tags>"""

            
xml_getNumberList = """<?xml version="1.0"?>
        <numeros>
            <numero>333</numero>
            <numero>777</numero>
            <numero>888</numero>
            <numero>87654321</numero>
        </numeros>
"""

xml_getProductCatalog = """<?xml version="1.0"?>
    <productos>
        <terminal>
            <id>1223</id>   
            <marca>CISCO</marca>
            <modelo>generic</modelo>
            <descripcion>bla bla bla</descripcion>
            <precio>1999666</precio>
            <existencia>23</existencia>
            <imagen>http://.../...</imagen> 
        </terminal>


        <terminal>
            <id>123</id>   
            <marca>nokia</marca>
            <modelo>N96</modelo>
            <descripcion>bla bla bla</descripcion>
            <precio>1999.666</precio>
            <existencia>23</existencia>
            <imagen>http://.../...</imagen> 
        </terminal>
        <terminal>
            <id>124</id>   
            <marca>nokia</marca>
            <modelo>N97</modelo>
            <descripcion>bla bla bla</descripcion>
            <precio>19.666</precio>
            <existencia>23</existencia>
            <imagen>http://.../...</imagen> 
        </terminal>  
    </productos>
    """


            
class Action(Base):
    """List of actions that users can make, related to roles, 
    if a role can perform an action and a user has that role
    the user can perform the action.
    ***NOTE***: To use this on each page you have to verify permitions."""
    __tablename__ = 'actions'

    id = Column(Integer, primary_key = True)
    name = Column(String, nullable=False)
    url = Column(String, nullable=False)

    def __repr__(self):
        return self.name+" en url: "+self.url

class UserRole(Base):
    """Here we have roles created by an administrator. This roles are assigned to users
    and specify what actions can be done by the user."""
    __tablename__ = 'user_roles'

    id = Column(Integer, primary_key = True)
    name = Column(String, unique=True, nullable=False)
    actions = relation("Action",secondary=role_actions)

    def __repr__(self):
        return self.name

class User(Base):
    """In this table we hold the info related to the login of the user of 
    the application"""
    __tablename__ = 'users'

    name = Column(String,primary_key = True, nullable=False)
    id = Column(Integer,Sequence('user_id_seq'), unique = True, nullable=False)
    pwd = Column(String, nullable=False)
    
    role_id = Column(Integer, ForeignKey('user_roles.id'))
    is_active = Column(Boolean)
    role = relation(UserRole, backref=backref('user_roles'))
    
    def __repr__(self):
        return self.name+" "

    def makeDBUser(self, session):
        """Create this user in the database. The user will be prefixed 'crm_'
        to avoid conflicts with other users on the database.
        This procedure doesnot commit, nor check for exceptions"""
        s = text("""CREATE USER crm_%s PASSWORD '%s'""" % (self.name,self.pwd))
        session.execute(s)
 
    def refreshDBPermitions(self, session):
        """This proc check what actions are allowed for this user
        and grants the necesary permitions in the database to make the actions
        possible.
        **NOTE**: This permitions over the database tables are hardcoded. if
        a page is added that needs access to the database, that acces need to be
        granted here.
        **NOTE2**: This proc doesnot commit and may raise a Exception
        **NOTE3**: This proc must be called in the following scenarios:
            when a user has been created/updated: for that user
            when roles are updated: for all the users with that role
            """   
        role = self.role
        if role is None or role.actions is None:
            return

        #Remove all permitions
        self.removeAllPerms(session)
        #Grant only allowed permitions
            
        for i in role.actions:
        
            if i.url == "/serve_client":
                s = text("GRANT SELECT,UPDATE,DELETE,INSERT ON lines_services_rel TO crm_%s" % (self.name))
                session.execute(s)            

                s = text("GRANT SELECT ON plans TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("GRANT SELECT,INSERT,UPDATE,DELETE ON lines_plans_rel TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("GRANT SELECT ON plans_services_rel TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("GRANT SELECT ON services TO crm_%s" % (self.name))
                session.execute(s)    
                s = text("GRANT SELECT ON clients TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("GRANT SELECT ON accounts TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("GRANT SELECT,INSERT,UPDATE,DELETE ON lines TO crm_%s" % (self.name))
                session.execute(s)            

                
                s = text("GRANT SELECT ON contracts TO crm_%s" % (self.name))
                session.execute(s)            


            if i.url == "/admin/user":
            
                s = text(" GRANT ALL PRIVILEGES ON DATABASE crm to crm_%s" % self.name)
                session.execute(s)
                s = text("GRANT SELECT ON role_actions_rel TO crm_%s" % (self.name))
                session.execute(s)            
                s = text("""GRANT INSERT,SELECT,UPDATE,DELETE ON users TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT ON user_roles TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT ON actions TO crm_%s""" % (self.name))
                session.execute(s)


            elif i.url == "/admin/user_role":

                s = text("GRANT INSERT,SELECT,UPDATE,DELETE ON role_actions_rel TO crm_%s" % (self.name))
                session.execute(s)                       
                s = text("""GRANT SELECT ON users TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,UPDATE,DELETE ON user_roles TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT ON actions TO crm_%s""" % (self.name))
                session.execute(s)

            elif i.url == "/admin/action":
                s = text("""GRANT SELECT ON user_roles TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON actions TO crm_%s""" % (self.name))
                session.execute(s)

            elif i.url == '/admin/module':
                s = text("""GRANT SELECT,UPDATE ON modules TO crm_%s""" % (self.name))
                session.execute(s)
            elif i.url == "/tables_admin/client_data":
                s = text("""GRANT SELECT,INSERT,UPDATE ON client_classification_catalog to crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON clients TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON companies TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE ON contacts TO crm_%s""" % (self.name))
                session.execute(s)

            elif i.url == "/tables_admin/new/client_edit":

                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON clients TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON companies TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE ON contacts TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE ON accounts TO crm_%s""" % (self.name))
                session.execute(s)
                
            elif i.url == "/tables_admin/new/account":

                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON clients TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON companies TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE ON contacts TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE ON accounts TO crm_%s""" % (self.name))
                session.execute(s)
                
            elif i.url == "/tables_admin/account_data":

                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON accounts TO crm_%s""" % (self.name))
                session.execute(s)
            
            elif i.url == "/tables_admin/new/contract":

                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON accounts TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON contracts TO crm_%s""" % (self.name))
                session.execute(s)
                
            elif i.url == "/tables_admin/new/add_category":

                s = text("""GRANT all ON client_category_catalog  TO crm_%s""" % (self.name))
                session.execute(s)
            elif i.url == "/tables_admin/category":

                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON client_category_catalog  TO crm_%s""" % (self.name))
                session.execute(s)
                s = text("""GRANT SELECT,INSERT,UPDATE,DELETE ON client_category  TO crm_%s""" % (self.name))
                session.execute(s)

    def makeAdmin(self, session):
        """Give this user the permitions over all the database.
        the tables are:
        accounts
        client_classification_catalog
        clients
        companies
        contacts
        contracts
        lines
        user_roles
        users
"""
        tables = {
                'accounts':'INSERT,UPDATE,SELECT',
                'client_classification_catalog':'INSERT,UPDATE,SELECT',
                'clients':'INSERT,UPDATE,SELECT',
                'companies':'INSERT,UPDATE,SELECT',
                'contacts':'INSERT,UPDATE,SELECT',
                'contracts':'INSERT,UPDATE,SELECT',
                'lines':'INSERT,UPDATE,SELECT',
                'user_roles':'INSERT,UPDATE,SELECT',
                'users':'INSERT,UPDATE,SELECT,DELETE',
                'actions':'INSERT,UPDATE,SELECT,DELETE',
                'role_actions_rel':'INSERT,UPDATE,SELECT,DELETE',
                'user_roles':'SELECT,UPDATE,DELETE',
                'user_roles_id_seq':'SELECT,UPDATE'
               }

        for table,perms in tables.iteritems():
            print """GRANT %s ON %s TO crm_%s WITH GRANT OPTION""" % (perms,table,self.name)
            s = text("""GRANT %s ON %s TO crm_%s WITH GRANT OPTION""" % (perms,table,self.name))
            session.execute(s)
        
    def removeAllPerms(self, session):
        """Revoke permition to acces tables in the db from this user
        On the tables:
        accounts
        client_classification_catalog
        clients
        companies
        contacts
        contracts
        lines
        user_roles
        users
        **NOTE**: This procedure does not commit change
    database
    templates
    s
"""
        tables = {
                'accounts':'ALL',
                'client_classification_catalog':'ALL',
                'clients':'ALL',
                'companies':'ALL',
                'contacts':'ALL',
                'contracts':'ALL',
                'lines':'ALL',
                'user_roles':'ALL',
                'users':'ALL',
                'actions':'ALL',
                'role_actions_rel':'ALL',
                'user_roles_id_seq':'ALL'
               }

        for table,perms in tables.iteritems():
            s = text("""REVOKE ALL ON %s FROM crm_%s""" % (table,self.name))
            session.execute(s)



class Contact(Base):
    """Holds the information of the contacts of an enterprise.
        the field description holdsa Description, comment, charge o whatever information you want to add to the contact.
        Relations: companies contact is a one to many relationsihp"""
    __tablename__ = 'contacts'

    id = Column(Integer, primary_key = True)
    name = Column(String, nullable=False)
    email = Column(String, nullable=False)
    telephone = Column(String, nullable=False)
    description = Column(String, nullable=False) 
    company_id = Column(Integer, ForeignKey('companies.id'))

    def __repr__(self):
        return "%s %s %s" % (self.name, self.email, self.telephone)

lines_plans = Table('lines_plans_rel', Base.metadata,
    Column('line_id', Integer, ForeignKey('lines.id')),
    Column('plan_id', Integer, ForeignKey('plans.id'))
)
    
plans_services = Table('plans_services_rel', Base.metadata,
    Column('plan_id', Integer, ForeignKey('plans.id')),
    Column('service_id', Integer, ForeignKey('services.id'))
)

lines_services = Table('lines_services_rel', Base.metadata,
    Column('line_id', Integer, ForeignKey('lines.id')),
    Column('service_id', Integer, ForeignKey('services.id'))
)

class Plan(Base):
    """Here we have the plan related information, this table will be
    updated from the plan catalog that the invoice module provide us via XML.
    this is a prospect for that XML."""
    
    __tablename__= "plans"
    
    id = Column(Integer, primary_key = True)
    name = Column(String)
    description = Column(String)
    services = relation("Service",secondary=plans_services)
    price = 0 #price is calculated from the services
    
    def __repr__(self):
        return self.name
       
    
class Line(Base):   
    """Here we hold the info of the line related to a contract
    one contract can have many lines
    Relation:
        line contract is a many to one."""

    __tablename__ = 'lines'

    id = Column(Integer, primary_key = True)
    number = Column(String, unique = True, nullable=False)
    contract_id = Column(Integer, ForeignKey('contracts.id'), nullable=False)

    plans = relation("Plan", secondary=lines_plans)
    product_id = Column(Integer, nullable=False) #el id del product que compro con la linea
    product_name = Column(String)
    services = relation("Service",secondary=lines_services)
    
    def __repr__(self):
        return "%s" % self.number

class Contract(Base):
    """Here we hold a relation between the customer and the agent
    and the services acquired.
    Relations:
        contract client is a one to one
        contract agent is a one to one
        contract line is a one to many"""
    __tablename__ = 'contracts'

    id = Column(Integer, primary_key = True)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=False)
    account_id = Column(Integer, ForeignKey('accounts.id'), nullable=False)
    agent_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    lines = relation(Line, primaryjoin=id == Line.contract_id, backref=("Lineas"))
    user = relation(User, backref=backref('users'))
    
    def __repr__(self):
        return "Contrato # %s" % self.id
    
class Account(Base):
    """A client can have many Accounts, an account holds the
    info of the person that will be billed.
    is_active tells if the acount is active
    Relations: client accounts is a one to many relationship"""
    __tablename__ = 'accounts'

    id = Column(Integer, primary_key = True)
    name = Column(String, nullable=False)
    address = Column(String, nullable=False)
    nit = Column(String, nullable=False)
    is_active = Column(Boolean, nullable=False)
    billing = Column(Integer, nullable=False)
    client_id = Column(Integer, ForeignKey('clients.id'), nullable=False)
    contracts = relation(Contract, primaryjoin=id == Contract.account_id)
    
    def __repr__(self):
        return "%s" % self.name
    
class ClientClassificationCatalog(Base):
    """Here we hold the clients possible classifications; i.e. 'VIP','Regular' ,chuck norris, etc."""
    __tablename__ = 'client_classification_catalog'
    id = Column(Integer, primary_key = True)
    name = Column(String, unique = True, nullable=False)
    def __repr__(self):
        return self.name+" "

class Client(Base):
    """Holds the personal info of the customers.
    creation date describes the day we inserted the client on the db
    if this client es a company birthdate is the foundation date of the company
    Relation: classification catalog"""
    __tablename__ = 'clients'
    
    id = Column(Integer, primary_key = True) #asdf
    name = Column(String, nullable=False)
    personal_id = Column(String, nullable=False) #cedula o numero de identificacion del como se llame el doc de identificacion
    address = Column(String, nullable=False)
    telephone = Column(String, nullable=False)
    email = Column(String, nullable=False)
    birthdate = Column(Date, nullable=False)
    category = None
    creation_date = Column(Date, server_default=text('NOW()'))

    classification_id = Column(Integer, ForeignKey('client_classification_catalog.id'), nullable=False)
    classification = relation(ClientClassificationCatalog, primaryjoin=classification_id == ClientClassificationCatalog.id)

    accounts = relation(Account, primaryjoin=id == Account.client_id)

    def set_category(self,session=None):
        if session==None:
            return False
        try:
            invoice_request = InvoiceRequest(session)
            amount=0
            mora=0
            date = session.query(ClientCategoryConfiguration).first().since
            dinero = session.query(ClientCategoryConfiguration).filter(ClientCategoryConfiguration.id==1).one().money

            lista = invoice_request.getInvoiceHistory(client_id=self.id ,date=date)
            for invoice in lista:
                if dinero == True:
                    amount = amount + int(invoice.amount)
                else:
                    if li.paid == False:
                        mora = mora + 1

            categories = session.query(ClientCategoryCatalog).all()
            if categories:
                for category in categories:
                    if ((amount >= category.rangea) and (amount <= category.rangeb)):
                        self.category=category.name
                        break
            if self.category is None:
                self.category='N/A'
                
            return True
        except Exception, e:
            self.category='N/A'
            print "##ERROR##: en categoria del cliente: %s" % e.args
class ClientCategoryConfiguration(Base):
    """money indicates whether we categorize the client by the amount paid to
      the company or by the months he hasn't paid. since determines the date we use to start counting """
    __tablename__ = 'client_category'

    id = Column(Integer, primary_key = True)
    money = Column(Boolean)
    since = Column(Date)

    def __repr__(self):
        return "[config money %s, since %s]" % (self.money,self.since)

class ClientCategoryCatalog(Base):
    
    __tablename__ = 'client_category_catalog'
    
    id = Column(Integer, primary_key = True)
    name = Column(String)
    rangea = Column(Integer)
    rangeb = Column(Integer)
    
    def __repr__(self):
        return "[catcatalog %s]" % self.name

    
class Company(Client):
    """If a client is an enterprise we need aditional info
    that we store here.
    Relation with client one to one"""
    __tablename__ = 'companies'
 
    id = Column(Integer, ForeignKey('clients.id'), primary_key = True) #enrealidad es client_id tmb
    web_site = Column(String, nullable=False)
    fax = Column(String, nullable=False)

    legal_repr_name = Column(String, nullable=False)
    legal_repr_email = Column(String, nullable=False)
    legal_repr_telephone = Column(String, nullable=False)
#la cedula del legal_repr se guarda en personal id de client

    client = relation(Client, primaryjoin=id == Client.id)
    contacts = relation(Contact, primaryjoin= id == Contact.company_id)

class Service(Base):
    """ 
    This table will be maintained though the XML received from the invoice module:
    """
    __tablename__ = "services"
    
    id = Column(Integer, primary_key = True)
    name = Column(String)
    description = Column(String)
    dimention = Column(String)
    network = Column(String)
    price = Column(String)
    size = Column(String)
    is_required = Column(Boolean)
    
    def __repr__(self):
        return self.name
       
class Module(Base):
    """In this table we hold the name of the module, 
    the addres of the module and the extencion of their pages.
    the tables of this entries arent made to be deleted nor inserted
    (except for the default entries that will be made), just for editing."""
    __tablename__ = 'modules'
    name = Column(String, primary_key=True)
    address = Column(String, nullable=False)
    ext = Column(String, default="", nullable=False)    


class DataBase(object):
    """We create a connection with the database. The root connection used by this 
    application is crmroot:crmpwd created by this commands in a terminal
    createdb -p 5432 crm
    createuser -P -p 5432 -U crmroot
    password: crmpwd
"""

    def __init__(self, user, pwd):
        self.engine = create_engine('postgres://%s:%s@localhost:5432/crm' % (user, pwd), echo=True)
        self.metadata = Base.metadata
        self.metadata.create_all(self.engine)
        self.Session = sessionmaker(bind=self.engine)   
        #databasename crm, 



class ModuleInteract():
    def start_element(self,name,attrs):
        pass
    def char_data(self,data):
        pass
    def end_element(self,data):
        pass

class Invoice(object):
    """This class is used when reading the invoice history """
    def __init__(self,id=None):
        self.id = id
        self.expiration_date = None
        self.paid = None
        self.amount = None
        self.balance = None
    
    def __repr__(self):
        return "[factura id = %s]" % self.id

class InvoiceRequest(ModuleInteract):
    #constants
    UP = 0
    DOWN = 1
    MODIFY = 2
    
    def __init__(self,session):
        self.p = None
        self.s = None        
        self.session = session
        self.module = session.query(Module).filter(Module.name == "Facturacion").one()
        self.last_obj = ""
        self.last_tag = ""

    def sincwithinvoicemodule(self, session):
        """This proc makes a request to the invoice module
        and fill this table with the information recieved 
        in XML"""

        p = xml.parsers.expat.ParserCreate()

        p.StartElementHandler = self.start_element
        p.EndElementHandler = self.end_element
        p.CharacterDataHandler = self.char_data

        module = session.query(Module).filter(Module.name == "Facturacion").one()

        request_url = module.address +"/telefonia/resourcePHP/catalogo"+module.ext
        try:
            if invoice_offline:
                xml_str = xml_sincwithinvoicemodule
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            self.session.commit()
        except Exception, e:
            self.session.rollback()
            print "##ERROR##: no se pudo obtener info de facturacion al sincronizar planes y servicios. return_url ="+request_url+" error=%s" % e.args
        
        
        
    def notifyChanges(
            self, 
            session=None, 
            action=None, 
            account_id=None):
        """
        Parameters:
            action: one of InvouceRequest.UP, InvoiceRequest.DOWN, InvoiceRequest.MODIFY
            
        The request is made to the url:
        
            
            http://xxxxx./telefonia/resourPHP/acciones.php?accion=A&cuenta=""&idcliente=""
        The arguments that that page recieve are:
            accion=A|B|M                UP, DOWN, MODIFY
            cuenta=""                   account_id
            
        """
        #TODO AAAaccion=A/B
        # cuenta
        # idcliente
        if action == self.UP:
            action = "A"
        elif action == self.DOWN:
            action = "B"
        elif action == self.MODIFY:
            action = "M"
        account = self.session.query(Account).filter(Account.id == account_id).first()
        
        request_url = self.module.address+"/telefonia/resourcePHP/acciones"+self.module.ext+"?accion=%s&cuenta=%s&idcliente=%s" % (action, account_id, account.client_id)

        try:
            
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            print "--------notificar a fact--------------------"+request_url
            if invoice_offline:
                xml_str = """<statusd>ok</status> """
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
                
        except Exception,e:
            print "##ERROR##: Invoice.notifychanges() req=%s   e=%s " % (request_url,e.args)
            return []
            

    def getInvoiceHistory(self, client_id = None, date = None):
        """returns a list of Invoice objects"""
 
        request_url = self.module.address + "/telefonia/resourcePHP/factura" + self.module.ext + "?cid=%s&fecha=%s" % (client_id, date)
        p = xml.parsers.expat.ParserCreate()
        p.StartElementHandler = self.start_element_hist
        p.EndElementHandler = self.end_element_hist
        p.CharacterDataHandler = self.char_data_hist

        self.history = []
        try:
            global offline_mode
            if offline_mode:
                xml_str = xml_getInvoiceHistory
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            return self.history
        except Exception, e:
            print "##Error##: en getInvoiceHistory, req_url = "+request_url+" Error=%s" % e.args
            return []

        
    def start_element_hist(self, name, attrs):
        name = name.strip().lower()
        self.last_tag = name
        if name == "factura":
            self.invoice = Invoice(attrs.get('cid'))
        print "-start-"+name

    def end_element_hist(self, name):
        name = name.strip().lower()
        print "---end---"+name
        if name == "factura":
            self.history.append(self.invoice)
        
    def char_data_hist(self, data):
        data = data.lstrip().rstrip().lower()
        if len(data) > 0:

            if self.last_tag == 'cuenta':
                self.invoice.account_id = data
            elif self.last_tag == 'fechaexpiracion':
                self.invoice.expiration_date = data
            elif self.last_tag == 'pago':
                self.invoice.paid = data
            elif self.last_tag == 'monto':
                self.invoice.amount = data
            elif self.last_tag == 'saldo':
                self.invoice.balance = data

                
    def start_element(self,name, attrs):
        self.last_tag = name
        if name == "plan":
            self.last_obj = name
            self.p = self.session.query(Plan).filter(Plan.id == attrs["idplan"]).first()
            if self.p is None:
                self.p = Plan()
                self.p.id = attrs["idplan"]
                #self.p.is_required = attrs["obligatorio"]
        elif name == "servicio":
            self.last_obj = name
            self.s = self.session.query(Service).filter(Service.id == attrs['idservicio']).first()
            if self.s is None:
                self.s = Service()
                self.s.id = attrs['idservicio']

            if attrs['obligatorio'].lower().strip() == 'si':
                self.s.is_required = True
            else:
                self.s.is_required = False
                self.s.is_required = False
        print "start %s" % name

    def end_element(self,name):
        if name == "plan":
            self.session.add(self.p)
        elif name == "servicio":
            self.session.add(self.s)
            self.p.services.append(self.s)
            
        print "end %s" % name
            
    def char_data(self,data):
        data = data.lstrip().rstrip().lower()
        if len(data) == 0:
            return 

        if self.last_tag == "nombre":
            if self.last_obj == "plan":
                self.p.name = data
            else:
                self.s.name = data
        elif self.last_tag == "descripcion":
            if self.last_obj == "plan":
                self.p.description = data
            else:
                self.s.description = data
            
        elif self.last_tag == "tamano":
            self.s.size = data
        elif self.last_tag == "dimension":
            self.s.dimention = data
        elif self.last_tag == "red":
            self.s.network = data
        elif self.last_tag == "valor":
            self.s.price = data

        
class Incidence(object):
    def __init__(self):
        self.id = None
        self.client_name = None
        self.cola = None
        self.description = None
        self.solution = None
        self.start_date = None
        self.end_date = None
        self.date = None
        
    def __repr__(self):
        return "[Incidence id=%s]" % self.id
        
class IncidenceRequest(ModuleInteract):
    """Comunicates with incidence module."""
    def __init__(self,session):
        self.module = session.query(Module).filter(Module.name == "Incidencias").one()
        self.last_obj = ""
        self.last_tag = ""
        self.incidence = None
        

    def getHistory(self, client_id = ""):
        request_url = self.module.address + " incidencias" + self.module.ext + "?ID=%s" % (client_id)
        p = xml.parsers.expat.ParserCreate()
        p.StartElementHandler = self.start_element_hist
        p.EndElementHandler = self.end_element_hist
        p.CharacterDataHandler = self.char_data_hist
        
        self.history = []
        try:
            global offline_mode
            if offline_mode:
                xml_str = xml_getIncidenceHistory
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            print xml_str
            t = p.Parse(xml_str)
            return self.history
        except Exception, e:
            print "##Error##: en getIncidenceHistory, req_url = "+request_url+" Error=%s" % e.args
            return None

    def start_element_hist(self,name,attrs):
        name = name.lstrip().rstrip().lower()
        if name == 'incidencias':
            self.incidence = Incidence()
        self.last_tag = name
    
    def char_data_hist(self,data):
        print "----------"+data
        data = data.lstrip().rstrip().lower()
        
        if len(data) > 0:
            
            if self.last_tag == 'id':
                self.incidence.id = data
            elif self.last_tag == 'cliente':
                self.incidence.client = data
            elif self.last_tag == 'cola':
                self.incidence.cola = data
            elif self.last_tag == 'incidencia':
                self.incidence.description = data
            elif self.last_tag == 'solucion':
                self.incidence.solution = data
            elif self.last_tag == 'inicio':
                self.incidence.start_date = data
            elif self.last_tag == 'fin':
                self.incidence.end_date = data
            elif self.last_tag == 'fecha':
                self.incidence.date = data
            
    def end_element_hist(self,name):
        name = name.strip().lower()
        if name == 'incidencias':
            self.history.append(self.incidence)
        
class Brand(object):
    def __init__(self,id):
        self.id = id
        self.name = ""

class Terminal(object):
    def __init__(self):
        self.id = None
        self.brand = None
        self.model = None
        self.description = None
        self.price = None
        self.existence = None
        self.image_url = None

    def __repr__(self):
        return "[Terminal (id,brand,model,desc,pric,ext,ima)=(%s,%s,%s,%s,%s,%s,%s)]" % (self.id,self.brand,self.model,self.description,self.price,self.existence,self.image_url)
        
class InventoryRequest(ModuleInteract):
    def __init__(self, session):
        self.last_obj = None
        self.numbers = []
        self.trans = {}
        self.product_list = []
        self.module = session.query(Module).filter(Module.name == "Inventario").one()
        self.module.ext = self.module.ext.strip('"')

    def getNumberList(self, number_quantity = 1):
        """
        This funcion makes a request to the url
        http://${inventory module root url}/host/services/numeros/show?n=number_quantity
        this url returns a xml file in the following format:
        <numeros>
            <numero>##</numero>*
        </numeros>
        Returns a list of strings, representing a list of numbers.
        if an exception was raised when making the request or parsing the xml file
        the args of the exception will be printed on the console, and the function will
        return None
        """
        p = xml.parsers.expat.ParserCreate()
        p.StartElementHandler = self.start_element_gnl
        p.EndElementHandler = self.end_element
        p.CharacterDataHandler = self.char_data_gnl

        request_url = self.module.address+"/services/numeros/show"+self.module.ext+"?n=%s" % number_quantity

        try:
            #global offline_mode
            if inventory_offline:
                xml_str = xml_getNumberList
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            return self.numbers
            
        except Exception, e:
            print "$$$---ERROR---$$$ Cannot communicate with inventory module request_url="+request_url 
            return None
        
        

    def transaction(self, url="", url_args=""):
        """access url and retrieve the transaction info in a dictionary """

        p = xml.parsers.expat.ParserCreate()

        p.StartElementHandler = self.start_element_trans
        p.EndElementHandler = self.end_element
        p.CharacterDataHandler = self.char_data_trans

        request_url = self.module.address + url + self.module.ext + url_args
        
        try:
            #global offline_mode
            if inventory_offline:
                xml_str = xml_transaction
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()        
            t = p.Parse(xml_str)
            return self.trans
            
        except Exception, e:
            print "##Error##: makin a transaction with inventory url=%s, error=%s" % (request_url, e.args)
            return None
        
    def getTags(self):
        """request to:
            http://${inventory root url}/services/terminales/tags
            return example:
            ['moviles','fijos','internet','blutut','wifi']
            or None if an exception is raised"""
        request_url = self.module.address+"/services/terminales/tags"+self.module.ext
        self.tags = []
        p = xml.parsers.expat.ParserCreate()
        p.StartElementHandler = self.start_element_tags
        p.EndElementHandler = self.end_element
        p.CharacterDataHandler = self.char_data_tags

        try:       
            if inventory_offline:
                xml_str = xml_getTags
            else:
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            
        except Exception, e:
            print "$$$---ERROR:---$$$: problems communicating with getTags url=%s error = %s" % (request_url,e.args)
            return None

        return self.tags

    def getBrandCatalog(self):
        """Request to inventory the existing brands to filter the prodcut catalog, 
        returns a list of Brand objects.
        """
 
        self.brands = None
        p = xml.parsers.expat.ParserCreate()

        p.StartElementHandler = self.start_element_brand
        p.EndElementHandler = self.end_element_brand
        p.CharacterDataHandler = self.char_data_brand

        self.brands = []
        
        try:
            request_url = self.module.address+"/services/terminales/marcas"+self.module.ext+""
            global inventory_offline
            if inventory_offline:
                xml_str = xml_getBrandCatalog
            else: 
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            return self.brands
            
        except Exception, e:

            print "$$$---ERROR:---$$$: problems communicating with inventory module in getBrandCatalog %s" % e
            return None

 
        
    def getProductCatalog(self, tags = "", marca = None, brand_id="", product_id = None):
        """Tags is a list of strings that filters the product catalog, 
           this tags must be requested from inventory before making a request here.
           returns a list of terminal objects, see the Terminal class
           products_ids is a list of string, product_id"""

        self.terminal = None
        p = xml.parsers.expat.ParserCreate()

        p.StartElementHandler = self.start_element_prod
        p.EndElementHandler = self.end_element_prod
        p.CharacterDataHandler = self.char_data_prod
        brand = ""
        if len(brand_id)>0:
            brand = 'marca='+brand_id

        try:
            if len(tags) > 0:            
                request_url = self.module.address+"/services/terminales"+self.module.ext+"?tags="+tags+"&"+brand
            else:
                request_url = self.module.address+"/services/terminales"+self.module.ext+"?"+brand

            if product_id:
                request_url = self.module.address+"/services/terminales"+self.module.ext+"?ids="+str(product_id)
            
            if inventory_offline:
                xml_str = xml_getProductCatalog
            else: 
                xml_str = urllib2.urlopen(urllib2.Request(url=request_url)).read()
            t = p.Parse(xml_str)
            print repr(self.product_list)+"---------PLIST---------"+request_url+"---------------"
            return self.product_list
            
        except Exception, e:
            print "$$$---ERROR:---$$$: in get product catalog return_url=%s error=%s" % (request_url,e.args)
            return None
        
        
 
 
    def start_element_tags(self,name, attrs):
        self.last_obj = name

    def char_data_tags(self,data):
        data = data.lstrip().rstrip().lower()
        if self.last_obj == "tag" and len(data.strip()) > 0:
            self.tags.append(data.strip())


    def start_element_brand(self,name, attrs):
        if name == "marca":
            self.brand = Brand(attrs.get('id'))
        self.last_tag = name            

     
    def char_data_brand(self,data):
        data = data.lstrip().rstrip().lower()
        if len(data) > 0:
            self.brand.name = data

    def end_element_brand(self,name):
        name = name.strip()
        if name == 'marca':
            self.brands.append(self.brand)
        
    def start_element_prod(self,name, attrs):
        if name == "terminal":
            self.terminal = Terminal()
        self.last_tag = name
        print "----------------" + name
    def char_data_prod(self,data):
        data = data.lstrip().rstrip().lower()
    
        if len(data) > 0:

            print self.last_tag, data, "$$$$$"

            if self.last_tag == "id":
                self.terminal.id = data

            elif self.last_tag == "marca":
                self.terminal.brand = data
            elif self.last_tag == "modelo":
                self.terminal.model = data
            elif self.last_tag == "descripcion":
                self.terminal.description = data
            elif self.last_tag == "precio":
                self.terminal.price = data
            elif self.last_tag == "existencia":
                self.terminal.existence = data
            elif self.last_tag == "imagen":
                self.terminal.image_url = data
                
    def end_element_prod(self,name):
        name = name.strip()

        if name == 'terminal':
            self.product_list.append(self.terminal)


    def start_element_trans(self,name, attrs):
        if name == 'transaccion':
            self.last_obj = name
            self.trans['attrs']=attrs
        elif name == 'status':
            self.last_obj = name
            pass
        elif name == 'mensaje':
            self.last_obj = name
            pass
        elif name == 'productos':
            self.last_obj = name
        elif name == 'terminal':
            if self.trans.get('terminals'):
                self.trans['terminals'].append(attrs)
            else:
                self.trans['terminals'] = [attrs]

    def char_data_trans(self,data):
        data = data.lstrip().rstrip().lower()
        if self.last_obj and len(data.strip()) > 0:
            self.trans[self.last_obj] = (data.strip())

    def start_element_gnl(self,name, attrs):
        if name == "numero":
            self.last_obj = name

    def char_data_gnl(self,data):
        data=data.lstrip().rstrip().lower()
        if self.last_obj and len(data.strip()) > 0:
            self.numbers.append(data.strip())
        
